import os
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import func, select, and_, or_
from sqlalchemy.orm import selectinload, joinedload
from datetime import datetime, timedelta
from typing import Dict, List, Any, Optional
import logging
from database.models import (
    User, Product, Order, Manufacturer, Model3D, 
    ProductionJob, Category, OrderStatus, JobStatus
)
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter

logger = logging.getLogger(__name__)


class ReportService:
    """报表和数据统计服务"""
    
    def __init__(self, session: AsyncSession):
        self.session = session
        self.upload_dir = "uploads/reports"
        # 确保上传目录存在
        os.makedirs(self.upload_dir, exist_ok=True)
    
    async def get_dashboard_stats(self) -> Dict[str, Any]:
        """获取仪表板统计数据"""
        try:
            # 用户统计
            total_users = await self.session.scalar(select(func.count(User.id)))
            active_users = await self.session.scalar(
                select(func.count(User.id)).where(User.is_active == True)
            )
            
            # 商品统计
            total_products = await self.session.scalar(select(func.count(Product.id)))
            active_products = await self.session.scalar(
                select(func.count(Product.id)).where(Product.is_active == True)
            )
            
            # 订单统计
            total_orders = await self.session.scalar(select(func.count(Order.id)))
            
            # 今日订单
            today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
            today_orders = await self.session.scalar(
                select(func.count(Order.id)).where(Order.created_at >= today)
            )
            
            # 今日收入
            today_revenue = await self.session.scalar(
                select(func.sum(Order.total_amount))
                .where(
                    and_(
                        Order.created_at >= today,
                        Order.status == OrderStatus.PAID
                    )
                )
            ) or 0
            
            # 3D模型统计
            total_models = await self.session.scalar(select(func.count(Model3D.id)))
            
            # 生产任务统计
            pending_jobs = await self.session.scalar(
                select(func.count(ProductionJob.id))
                .where(ProductionJob.status == JobStatus.PENDING)
            )
            in_progress_jobs = await self.session.scalar(
                select(func.count(ProductionJob.id))
                .where(ProductionJob.status == JobStatus.IN_PROGRESS)
            )
            completed_jobs = await self.session.scalar(
                select(func.count(ProductionJob.id))
                .where(ProductionJob.status == JobStatus.COMPLETED)
            )
            
            return {
                "users": {
                    "total": total_users,
                    "active": active_users,
                    "inactive": total_users - active_users
                },
                "products": {
                    "total": total_products,
                    "active": active_products,
                    "inactive": total_products - active_products
                },
                "orders": {
                    "total": total_orders,
                    "today": today_orders,
                    "today_revenue": float(today_revenue)
                },
                "models": {
                    "total": total_models
                },
                "production_jobs": {
                    "pending": pending_jobs,
                    "in_progress": in_progress_jobs,
                    "completed": completed_jobs
                }
            }
            
        except Exception as e:
            logger.error(f"获取仪表板统计数据失败: {e}")
            return {
                "users": {"total": 0, "active": 0, "inactive": 0},
                "products": {"total": 0, "active": 0, "inactive": 0},
                "orders": {"total": 0, "today": 0, "today_revenue": 0.0},
                "models": {"total": 0},
                "production_jobs": {"pending": 0, "in_progress": 0, "completed": 0}
            }
    
    async def get_order_stats_by_status(self) -> Dict[str, int]:
        """获取订单状态统计"""
        try:
            result = {}
            for status in OrderStatus:
                count = await self.session.scalar(
                    select(func.count(Order.id)).where(Order.status == status)
                )
                result[status.value] = count
            return result
        except Exception as e:
            logger.error(f"获取订单状态统计失败: {e}")
            return {status.value: 0 for status in OrderStatus}
    
    async def get_revenue_stats(self, days: int = 30) -> Dict[str, Any]:
        """获取收入统计"""
        try:
            end_date = datetime.now()
            start_date = end_date - timedelta(days=days)
            
            # 按日期分组统计收入
            revenue_by_date = []
            current_date = start_date
            
            while current_date <= end_date:
                next_date = current_date + timedelta(days=1)
                daily_revenue = await self.session.scalar(
                    select(func.sum(Order.total_amount))
                    .where(
                        and_(
                            Order.created_at >= current_date,
                            Order.created_at < next_date,
                            Order.status == OrderStatus.PAID
                        )
                    )
                ) or 0
                
                revenue_by_date.append({
                    "date": current_date.strftime("%Y-%m-%d"),
                    "revenue": float(daily_revenue)
                })
                
                current_date = next_date
            
            # 总收入
            total_revenue = await self.session.scalar(
                select(func.sum(Order.total_amount))
                .where(
                    and_(
                        Order.created_at >= start_date,
                        Order.created_at <= end_date,
                        Order.status == OrderStatus.PAID
                    )
                )
            ) or 0
            
            # 平均订单金额
            paid_orders_count = await self.session.scalar(
                select(func.count(Order.id))
                .where(
                    and_(
                        Order.created_at >= start_date,
                        Order.created_at <= end_date,
                        Order.status == OrderStatus.PAID
                    )
                )
            ) or 0
            
            avg_order_value = float(total_revenue / paid_orders_count) if paid_orders_count > 0 else 0
            
            return {
                "period": f"{days}天",
                "total_revenue": float(total_revenue),
                "avg_order_value": avg_order_value,
                "paid_orders_count": paid_orders_count,
                "daily_revenue": revenue_by_date
            }
            
        except Exception as e:
            logger.error(f"获取收入统计失败: {e}")
            return {
                "period": f"{days}天",
                "total_revenue": 0.0,
                "avg_order_value": 0.0,
                "paid_orders_count": 0,
                "daily_revenue": []
            }
    
    async def get_product_stats(self) -> Dict[str, Any]:
        """获取商品统计"""
        try:
            # 按分类统计商品数量
            category_stats = []
            categories = await self.session.execute(select(Category))
            for category in categories.scalars():
                product_count = await self.session.scalar(
                    select(func.count(Product.id))
                    .where(Product.category_id == category.id)
                )
                category_stats.append({
                    "category_name": category.name,
                    "product_count": product_count,
                    "is_active": category.is_active
                })
            
            # 商品库存统计
            low_stock_products = await self.session.scalar(
                select(func.count(Product.id))
                .where(Product.stock_quantity <= 10)
            )
            
            out_of_stock_products = await self.session.scalar(
                select(func.count(Product.id))
                .where(Product.stock_quantity == 0)
            )
            
            # 3D模板统计
            products_with_3d_template = await self.session.scalar(
                select(func.count(Product.id))
                .where(Product.template_3d_url.is_not(None))
            )
            
            return {
                "by_category": category_stats,
                "low_stock": low_stock_products,
                "out_of_stock": out_of_stock_products,
                "with_3d_template": products_with_3d_template
            }
            
        except Exception as e:
            logger.error(f"获取商品统计失败: {e}")
            return {
                "by_category": [],
                "low_stock": 0,
                "out_of_stock": 0,
                "with_3d_template": 0
            }
    
    async def get_user_stats(self) -> Dict[str, Any]:
        """获取用户统计"""
        try:
            # 按角色统计用户
            user_by_role = {}
            from database.models import UserRole
            for role in UserRole:
                count = await self.session.scalar(
                    select(func.count(User.id)).where(User.role == role)
                )
                user_by_role[role.value] = count
            
            # 最近注册用户数（7天）
            seven_days_ago = datetime.now() - timedelta(days=7)
            recent_users = await self.session.scalar(
                select(func.count(User.id))
                .where(User.created_at >= seven_days_ago)
            )
            
            # 用户增长趋势（最近30天）
            user_growth = []
            for i in range(30):
                date = datetime.now() - timedelta(days=i)
                start_of_day = date.replace(hour=0, minute=0, second=0, microsecond=0)
                end_of_day = date.replace(hour=23, minute=59, second=59, microsecond=999999)
                
                daily_users = await self.session.scalar(
                    select(func.count(User.id))
                    .where(
                        and_(
                            User.created_at >= start_of_day,
                            User.created_at <= end_of_day
                        )
                    )
                )
                
                user_growth.append({
                    "date": date.strftime("%Y-%m-%d"),
                    "new_users": daily_users
                })
            
            user_growth.reverse()  # 按日期升序排列
            
            return {
                "by_role": user_by_role,
                "recent_registrations": recent_users,
                "growth_trend": user_growth
            }
            
        except Exception as e:
            logger.error(f"获取用户统计失败: {e}")
            return {
                "by_role": {},
                "recent_registrations": 0,
                "growth_trend": []
            }
    
    async def get_production_stats(self) -> Dict[str, Any]:
        """获取生产统计"""
        try:
            # 按状态统计生产任务
            job_by_status = {}
            for status in JobStatus:
                count = await self.session.scalar(
                    select(func.count(ProductionJob.id))
                    .where(ProductionJob.status == status)
                )
                job_by_status[status.value] = count
            
            # 按厂商统计生产任务
            manufacturer_stats = []
            manufacturers = await self.session.execute(select(Manufacturer))
            for manufacturer in manufacturers.scalars():
                job_count = await self.session.scalar(
                    select(func.count(ProductionJob.id))
                    .where(ProductionJob.manufacturer_id == manufacturer.id)
                )
                avg_rating = manufacturer.rating if manufacturer.rating else 0
                manufacturer_stats.append({
                    "manufacturer_name": manufacturer.company_name,
                    "job_count": job_count,
                    "rating": avg_rating,
                    "is_active": manufacturer.is_active
                })
            
            # 生产效率统计（最近30天）
            thirty_days_ago = datetime.now() - timedelta(days=30)
            completed_jobs = await self.session.execute(
                select(ProductionJob)
                .where(
                    and_(
                        ProductionJob.status == JobStatus.COMPLETED,
                        ProductionJob.completed_at >= thirty_days_ago
                    )
                )
            )
            
            total_completion_time = 0
            job_count = 0
            
            for job in completed_jobs.scalars():
                if job.started_at and job.completed_at:
                    completion_time = (job.completed_at - job.started_at).total_seconds() / 3600  # 小时
                    total_completion_time += completion_time
                    job_count += 1
            
            avg_completion_time = total_completion_time / job_count if job_count > 0 else 0
            
            return {
                "jobs_by_status": job_by_status,
                "by_manufacturer": manufacturer_stats,
                "efficiency": {
                    "avg_completion_time_hours": avg_completion_time,
                    "completed_jobs_count": job_count
                }
            }
            
        except Exception as e:
            logger.error(f"获取生产统计失败: {e}")
            return {
                "jobs_by_status": {},
                "by_manufacturer": [],
                "efficiency": {
                    "avg_completion_time_hours": 0,
                    "completed_jobs_count": 0
                }
            }
    
    async def get_monthly_report(self, year: int, month: int) -> Dict[str, Any]:
        """获取月度报告"""
        try:
            # 计算月份范围
            start_date = datetime(year, month, 1)
            if month == 12:
                end_date = datetime(year + 1, 1, 1)
            else:
                end_date = datetime(year, month + 1, 1)
            
            # 月度订单统计
            monthly_orders = await self.session.scalar(
                select(func.count(Order.id))
                .where(
                    and_(
                        Order.created_at >= start_date,
                        Order.created_at < end_date
                    )
                )
            )
            
            # 月度收入
            monthly_revenue = await self.session.scalar(
                select(func.sum(Order.total_amount))
                .where(
                    and_(
                        Order.created_at >= start_date,
                        Order.created_at < end_date,
                        Order.status == OrderStatus.PAID
                    )
                )
            ) or 0
            
            # 月度生产任务
            monthly_jobs = await self.session.scalar(
                select(func.count(ProductionJob.id))
                .where(
                    and_(
                        ProductionJob.created_at >= start_date,
                        ProductionJob.created_at < end_date
                    )
                )
            )
            
            # 月度注册用户
            monthly_users = await self.session.scalar(
                select(func.count(User.id))
                .where(
                    and_(
                        User.created_at >= start_date,
                        User.created_at < end_date
                    )
                )
            )
            
            return {
                "period": f"{year}年{month}月",
                "orders": monthly_orders,
                "revenue": float(monthly_revenue),
                "production_jobs": monthly_jobs,
                "new_users": monthly_users
            }
            
        except Exception as e:
            logger.error(f"获取月度报告失败: {e}")
            return {
                "period": f"{year}年{month}月",
                "orders": 0,
                "revenue": 0.0,
                "production_jobs": 0,
                "new_users": 0
            }
    
    async def export_report(self, report_type: str, format: str = "json", start_date: datetime = None, end_date: datetime = None) -> str:
        """导出报表
        
        Args:
            report_type: 报表类型 (all, orders, users, products, production, revenue)
            format: 导出格式 (json, xlsx, excel)
            start_date: 开始日期，默认为30天前
            end_date: 结束日期，默认为当前时间
            
        Returns:
            生成的文件路径
        """
        try:
            # 处理字符串日期参数
            if isinstance(start_date, str):
                start_date = datetime.strptime(start_date, '%Y-%m-%d')
            if isinstance(end_date, str):
                end_date = datetime.strptime(end_date, '%Y-%m-%d')
                
            if start_date is None:
                start_date = datetime.now() - timedelta(days=30)
            if end_date is None:
                end_date = datetime.now()
            
            filename = f"{report_type}_report_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
            
            if format.lower() == "json":
                data = await self._generate_report_data(report_type, start_date, end_date)
                filepath = os.path.join(self.upload_dir, f"{filename}.json")
                with open(filepath, 'w', encoding='utf-8') as f:
                    import json
                    json.dump(data, f, ensure_ascii=False, indent=2, default=str)
                return f"/uploads/reports/{filename}.json"
            
            elif format.lower() in ["xlsx", "excel"]:
                wb = Workbook()
                
                # 设置样式
                header_font = Font(bold=True, color="FFFFFF")
                header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
                border = Border(
                    left=Side(style='thin'),
                    right=Side(style='thin'),
                    top=Side(style='thin'),
                    bottom=Side(style='thin')
                )
                alignment = Alignment(horizontal="center", vertical="center")
                
                try:
                    # 根据报表类型调用对应的导出方法
                    if report_type == "all" or report_type == "dashboard":
                        await self._export_all_reports(wb, start_date, end_date, header_font, header_fill, border, alignment)
                    elif report_type == "orders":
                        await self._export_order_report(wb, start_date, end_date, header_font, header_fill, border, alignment)
                    elif report_type == "revenue":
                        await self._export_revenue_report(wb, start_date, end_date, header_font, header_fill, border, alignment)
                    elif report_type == "products":
                        await self._export_product_report(wb, start_date, end_date, header_font, header_fill, border, alignment)
                    elif report_type == "users":
                        await self._export_user_report(wb, start_date, end_date, header_font, header_fill, border, alignment)
                    elif report_type == "production":
                        await self._export_production_report(wb, start_date, end_date, header_font, header_fill, border, alignment)
                    else:
                        raise ValueError(f"不支持的报表类型: {report_type}")
                except Exception as export_error:
                    logger.error(f"报表导出方法执行失败 (type: {report_type}): {export_error}")
                    logger.error(f"错误类型: {type(export_error).__name__}")
                    import traceback
                    logger.error(f"错误堆栈: {traceback.format_exc()}")
                    raise export_error
                
                filepath = os.path.join(self.upload_dir, f"{filename}.xlsx")
                wb.save(filepath)
                return f"/uploads/reports/{filename}.xlsx"
            
            else:
                raise ValueError(f"不支持的格式: {format}")
                
        except Exception as e:
            logger.error(f"导出报表失败: {e}")
            logger.error(f"错误类型: {type(e).__name__}")
            import traceback
            logger.error(f"错误堆栈: {traceback.format_exc()}")
            raise Exception(f"导出报表失败: {report_type}")
    
    async def _export_all_reports(self, wb: Workbook, start_date: datetime, end_date: datetime, 
                                 header_font: Font, header_fill: PatternFill, border: Border, 
                                 alignment: Alignment):
        """导出所有报表"""
        try:
            # 概览工作表
            ws = wb.create_sheet("报表概览")  # 使用create_sheet而不是active
            
            # 获取统计数据
            dashboard_stats = await self.get_dashboard_stats()
            order_stats = await self.get_order_stats_by_status()
            user_stats = await self.get_user_stats()
            product_stats = await self.get_product_stats()
            production_stats = await self.get_production_stats()
            
            # 写入概览数据
            ws['A1'] = "3D模型生产管理系统 - 数据报表概览"
            ws['A1'].font = Font(size=16, bold=True)
            ws.merge_cells('A1:E1')
            ws['A1'].alignment = alignment
            
            ws['A3'] = "统计时间范围:"
            ws['B3'] = f"{start_date.strftime('%Y-%m-%d')} 至 {end_date.strftime('%Y-%m-%d')}"
            ws['A4'] = "生成时间:"
            ws['B4'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            ws['A6'] = "关键指标"
            ws['A6'].font = header_font
            ws['A6'].fill = header_fill
            ws['A6'].alignment = alignment
            ws.merge_cells('A6:B6')
            
            ws['A7'] = "总用户数"
            ws['B7'] = dashboard_stats.get('users', {}).get('total', 0)
            ws['A8'] = "活跃用户数"
            ws['B8'] = dashboard_stats.get('users', {}).get('active', 0)
            ws['A9'] = "总订单数"
            ws['B9'] = dashboard_stats.get('orders', {}).get('total', 0)
            ws['A10'] = "今日订单数"
            ws['B10'] = dashboard_stats.get('orders', {}).get('today', 0)
            ws['A11'] = "今日收入"
            ws['B11'] = f"¥{dashboard_stats.get('orders', {}).get('today_revenue', 0):,.2f}"
            ws['A12'] = "总商品数"
            ws['B12'] = dashboard_stats.get('products', {}).get('total', 0)
            ws['A13'] = "3D模型数"
            ws['B13'] = dashboard_stats.get('models', {}).get('total', 0)
            ws['A14'] = "生产任务数"
            ws['B14'] = dashboard_stats.get('production_jobs', {}).get('total', 0)
            ws['A15'] = "待处理任务"
            ws['B15'] = dashboard_stats.get('production_jobs', {}).get('pending', 0)
            ws['A16'] = "处理中任务"
            ws['B16'] = dashboard_stats.get('production_jobs', {}).get('in_progress', 0)
            ws['A17'] = "已完成任务"
            ws['B17'] = dashboard_stats.get('production_jobs', {}).get('completed', 0)
            
            # 订单状态统计
            ws['D6'] = "订单状态统计"
            ws['D6'].font = header_font
            ws['D6'].fill = header_fill
            ws['D6'].alignment = alignment
            ws.merge_cells('D6:E6')
            
            row = 7
            for status, count in order_stats.items():
                ws[f'D{row}'] = status
                ws[f'E{row}'] = count
                row += 1
            
            # 设置列宽
            for col in range(1, 6):
                ws.column_dimensions[get_column_letter(col)].width = 20
            
            # 调用各个子报表方法，创建详细数据工作表
            await self._export_order_report(wb, start_date, end_date, header_font, header_fill, border, alignment)
            await self._export_user_report(wb, start_date, end_date, header_font, header_fill, border, alignment)
            await self._export_product_report(wb, start_date, end_date, header_font, header_fill, border, alignment)
            await self._export_production_report(wb, start_date, end_date, header_font, header_fill, border, alignment)
            await self._export_revenue_report(wb, start_date, end_date, header_font, header_fill, border, alignment)
        except Exception as e:
            logger.error(f"导出所有报表失败: {str(e)}")
            logger.error(f"错误类型: {type(e).__name__}")
            import traceback
            logger.error(f"错误堆栈: {traceback.format_exc()}")
            raise e
    
    async def _export_order_report(self, wb: Workbook, start_date: datetime, end_date: datetime,
                                  header_font: Font, header_fill: PatternFill, border: Border,
                                  alignment: Alignment):
        """导出订单报表"""
        ws = wb.create_sheet("订单报表")
        
        # 获取订单数据
        from sqlalchemy import select, and_
        
        stmt = select(Order).options(
            selectinload(Order.user),
            selectinload(Order.product)
        ).where(
            and_(Order.created_at >= start_date, Order.created_at <= end_date)
        ).order_by(Order.created_at.desc())
        
        result = await self.session.execute(stmt)
        orders = result.scalars().all()
        
        # 写入表头
        headers = ['订单号', '用户', '总金额', '状态', '创建时间', '商品数量', '商品名称']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = alignment
            cell.border = border
        
        # 写入数据
        for row, order in enumerate(orders, 2):
            ws.cell(row=row, column=1, value=order.order_number)
            ws.cell(row=row, column=2, value=order.user.username if order.user else '未知用户')
            ws.cell(row=row, column=3, value=float(order.total_amount))
            ws.cell(row=row, column=4, value=order.status.value)
            ws.cell(row=row, column=5, value=order.created_at.strftime('%Y-%m-%d %H:%M'))
            ws.cell(row=row, column=6, value=order.quantity)
            ws.cell(row=row, column=7, value=order.product.name if order.product else '未知商品')
            
            # 设置边框
            for col in range(1, 8):
                ws.cell(row=row, column=col).border = border
        
        # 设置列宽
        column_widths = [15, 20, 12, 12, 20, 12, 40]
        for col, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(col)].width = width
        
        # 添加统计汇总
        total_orders = len(orders)
        total_revenue = sum(float(order.total_amount) for order in orders)
        
        ws.cell(row=total_orders + 3, column=1, value="统计汇总")
        ws.cell(row=total_orders + 3, column=1).font = header_font
        ws.cell(row=total_orders + 3, column=1).fill = header_fill
        
        ws.cell(row=total_orders + 4, column=1, value="总订单数")
        ws.cell(row=total_orders + 4, column=2, value=total_orders)
        ws.cell(row=total_orders + 5, column=1, value="总收入")
        ws.cell(row=total_orders + 5, column=2, value=f"¥{total_revenue:,.2f}")
        ws.cell(row=total_orders + 6, column=1, value="平均订单金额")
        ws.cell(row=total_orders + 6, column=2, value=f"¥{total_revenue/total_orders:,.2f}" if total_orders > 0 else "¥0.00")
    
    async def _export_user_report(self, wb: Workbook, start_date: datetime, end_date: datetime,
                                 header_font: Font, header_fill: PatternFill, border: Border,
                                 alignment: Alignment):
        """导出用户报表"""
        ws = wb.create_sheet("用户报表")
        
        # 获取用户数据
        from sqlalchemy import select, func, and_
        
        stmt = select(User).options(
            selectinload(User.orders)
        ).order_by(User.created_at.desc())
        
        result = await self.session.execute(stmt)
        users = result.scalars().all()
        
        # 写入表头
        headers = ['用户名', '邮箱', '注册时间', '最后登录', '订单数', '总消费', '用户类型']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = alignment
            cell.border = border
        
        # 写入数据
        for row, user in enumerate(users, 2):
            order_count = len(user.orders)
            total_spent = sum(float(order.total_amount) for order in user.orders)
            
            # 用户类型判断
            if order_count == 0:
                user_type = "新用户"
            elif order_count <= 5:
                user_type = "普通用户"
            else:
                user_type = "VIP用户"
            
            ws.cell(row=row, column=1, value=user.username)
            ws.cell(row=row, column=2, value=user.email)
            ws.cell(row=row, column=3, value=user.created_at.strftime('%Y-%m-%d %H:%M'))
            ws.cell(row=row, column=4, value=user.updated_at.strftime('%Y-%m-%d %H:%M') if user.updated_at else '从未登录')
            ws.cell(row=row, column=5, value=order_count)
            ws.cell(row=row, column=6, value=f"¥{total_spent:,.2f}")
            ws.cell(row=row, column=7, value=user_type)
            
            # 设置边框
            for col in range(1, 8):
                ws.cell(row=row, column=col).border = border
        
        # 设置列宽
        column_widths = [20, 30, 20, 20, 12, 15, 15]
        for col, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(col)].width = width
    
    async def _export_product_report(self, wb: Workbook, start_date: datetime, end_date: datetime,
                                    header_font: Font, header_fill: PatternFill, border: Border,
                                    alignment: Alignment):
        """导出商品报表"""
        ws = wb.create_sheet("商品报表")
        
        try:
            # 获取商品数据
            from sqlalchemy import select, func, and_
            
            stmt = select(Product).options(
                selectinload(Product.category)
            ).order_by(Product.created_at.desc())
            
            result = await self.session.execute(stmt)
            products = result.scalars().all()
            
            # 写入表头
            headers = ['商品名称', '分类', '价格', '状态', '创建时间', '总销量', '总收入']
            for col, header in enumerate(headers, 1):
                cell = ws.cell(row=1, column=col, value=header)
                cell.font = header_font
                cell.fill = header_fill
                cell.alignment = alignment
                cell.border = border
            
            # 写入数据
            for row, product in enumerate(products, 2):
                try:
                    # 计算销量和收入（只统计指定时间范围内的订单）
                    total_sales = 0
                    total_revenue = 0
                    
                    # 获取商品的订单数据
                    orders_stmt = select(Order).where(
                        and_(
                            Order.product_id == product.id,
                            Order.created_at >= start_date,
                            Order.created_at <= end_date
                        )
                    )
                    orders_result = await self.session.execute(orders_stmt)
                    orders = orders_result.scalars().all()
                    
                    for order in orders:
                        total_sales += order.quantity
                        total_revenue += float(order.total_amount)
                    
                    ws.cell(row=row, column=1, value=product.name)
                    ws.cell(row=row, column=2, value=product.category.name if product.category else '未分类')
                    ws.cell(row=row, column=3, value=float(product.price))
                    ws.cell(row=row, column=4, value='活跃' if product.is_active else '停用')
                    ws.cell(row=row, column=5, value=product.created_at.strftime('%Y-%m-%d %H:%M'))
                    ws.cell(row=row, column=6, value=total_sales)
                    ws.cell(row=row, column=7, value=f"¥{total_revenue:,.2f}")
                    
                    # 设置边框
                    for col in range(1, 8):
                        ws.cell(row=row, column=col).border = border
                        
                except Exception as row_error:
                    logger.error(f"写入商品行数据失败 (product_id: {product.id}): {row_error}")
                    # 继续处理下一行，不中断整个报表生成
                    continue
            
            # 设置列宽
            column_widths = [30, 20, 12, 12, 20, 12, 15]
            for col, width in enumerate(column_widths, 1):
                ws.column_dimensions[get_column_letter(col)].width = width
                
        except Exception as e:
            logger.error(f"导出商品报表失败: {str(e)}")
            logger.error(f"错误类型: {type(e).__name__}")
            import traceback
            logger.error(f"错误堆栈: {traceback.format_exc()}")
            raise e
    
    async def _export_production_report(self, wb: Workbook, start_date: datetime, end_date: datetime,
                                       header_font: Font, header_fill: PatternFill, border: Border,
                                       alignment: Alignment):
        """导出生产报表"""
        ws = wb.create_sheet("生产报表")
        
        try:
            # 表头
            headers = ['任务ID', '订单ID', '产品名称', '3D模型', '厂商', '状态', '优先级', '创建时间', '更新时间', '完成时间', '备注']
            for col, header in enumerate(headers, 1):
                cell = ws.cell(row=1, column=col)
                cell.value = header
                cell.font = header_font
                cell.fill = header_fill
                cell.alignment = alignment
                cell.border = border
            
            # 查询生产任务数据
            query = select(ProductionJob).options(
                joinedload(ProductionJob.order),
                joinedload(ProductionJob.manufacturer)
            ).filter(
                ProductionJob.created_at >= start_date,
                ProductionJob.created_at <= end_date
            ).order_by(ProductionJob.created_at.desc())
            
            result = await self.session.execute(query)
            jobs = result.scalars().all()
            
            # 写入数据
            for row_idx, job in enumerate(jobs, 2):
                try:
                    ws.cell(row=row_idx, column=1, value=job.id)  # 任务ID
                    ws.cell(row=row_idx, column=2, value=job.order_id)  # 订单ID
                    
                    # 产品名称
                    product_name = '未知产品'
                    if job.order and job.order.product:
                        try:
                            product_name = getattr(job.order.product, 'name', '未知产品')
                        except Exception as product_error:
                            logger.warning(f"获取产品名称失败 (job_id: {job.id}): {product_error}")
                            product_name = '产品名称获取失败'
                    ws.cell(row=row_idx, column=3, value=product_name)
                    
                    # 3D模型 - 安全获取模型名称
                    model_name = '无模型'
                    if job.order and job.order.model_3d:
                        try:
                            if hasattr(job.order.model_3d, 'name'):
                                model_name = getattr(job.order.model_3d, 'name', '未知模型')
                            elif hasattr(job.order.model_3d, 'original_filename'):
                                model_name = getattr(job.order.model_3d, 'original_filename', '未知模型')
                            else:
                                model_name = f"模型_{job.order.model_3d.id}"
                        except Exception as model_error:
                            logger.warning(f"获取模型名称失败 (job_id: {job.id}): {model_error}")
                            model_name = '模型名称获取失败'
                    
                    ws.cell(row=row_idx, column=4, value=model_name)  # 3D模型
                    ws.cell(row=row_idx, column=5, value=job.manufacturer.company_name if job.manufacturer else '未分配')  # 厂商
                    ws.cell(row=row_idx, column=6, value=job.status.value)  # 状态
                    ws.cell(row=row_idx, column=7, value=job.priority)  # 优先级
                    ws.cell(row=row_idx, column=8, value=job.created_at.strftime('%Y-%m-%d %H:%M:%S') if job.created_at else '')  # 创建时间
                    ws.cell(row=row_idx, column=9, value=job.updated_at.strftime('%Y-%m-%d %H:%M:%S') if job.updated_at else '')  # 更新时间
                    ws.cell(row=row_idx, column=10, value=job.completed_at.strftime('%Y-%m-%d %H:%M:%S') if job.completed_at else '')  # 完成时间
                    ws.cell(row=row_idx, column=11, value=job.notes or '')  # 备注
                except Exception as row_error:
                    logger.error(f"写入生产任务行数据失败 (job_id: {job.id}): {row_error}")
                    # 继续处理下一行，不中断整个报表生成
                    continue
            
            # 设置列宽
            for col in range(1, len(headers) + 1):
                ws.column_dimensions[get_column_letter(col)].width = 15
            
            # 添加统计汇总
            stats_row = len(jobs) + 3
            ws.cell(row=stats_row, column=1, value="统计汇总")
            ws.cell(row=stats_row, column=1).font = header_font
            ws.cell(row=stats_row, column=1).fill = header_fill
            
            # 统计各状态的任务数量
            status_counts = {}
            total_jobs = len(jobs)
            
            for job in jobs:
                status = job.status.value
                status_counts[status] = status_counts.get(status, 0) + 1
            
            ws.cell(row=stats_row + 1, column=1, value="总任务数")
            ws.cell(row=stats_row + 1, column=2, value=total_jobs)
            
            row_offset = 2
            for status, count in status_counts.items():
                ws.cell(row=stats_row + row_offset, column=1, value=f"{status}")
                ws.cell(row=stats_row + row_offset, column=2, value=count)
                row_offset += 1
                
        except Exception as e:
            logger.error(f"导出生产报表失败: {str(e)}")
            logger.error(f"错误类型: {type(e).__name__}")
            import traceback
            logger.error(f"错误堆栈: {traceback.format_exc()}")
            raise e
    
    async def _export_revenue_report(self, wb: Workbook, start_date: datetime, end_date: datetime,
                                   header_font: Font, header_fill: PatternFill, border: Border,
                                   alignment: Alignment):
        """导出收入报表"""
        ws = wb.create_sheet("收入报表")  # 使用create_sheet避免active问题
        
        # 获取收入数据
        revenue_stats = await self.get_revenue_stats((end_date - start_date).days)
        
        # 写入表头
        ws['A1'] = "收入统计报表"
        ws['A1'].font = Font(size=16, bold=True)
        ws.merge_cells('A1:E1')
        ws['A1'].alignment = alignment
        
        ws['A3'] = "统计时间范围:"
        ws['B3'] = f"{start_date.strftime('%Y-%m-%d')} 至 {end_date.strftime('%Y-%m-%d')}"
        ws['A4'] = "生成时间:"
        ws['B4'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        # 总收入统计
        ws['A6'] = "总收入统计"
        ws['A6'].font = header_font
        ws['A6'].fill = header_fill
        ws['A6'].alignment = alignment
        ws.merge_cells('A6:B6')
        
        ws['A7'] = "总收入"
        ws['B7'] = f"¥{revenue_stats.get('total_revenue', 0):,.2f}"
        ws['A8'] = "平均订单金额"
        ws['B8'] = f"¥{revenue_stats.get('avg_order_value', 0):,.2f}"
        ws['A9'] = "已支付订单数"
        ws['B9'] = f"{revenue_stats.get('paid_orders_count', 0)}个"
        
        # 每日收入明细
        ws['A11'] = "每日收入明细"
        ws['A11'].font = header_font
        ws['A11'].fill = header_fill
        ws['A11'].alignment = alignment
        ws.merge_cells('A11:C11')
        
        # 写入表头
        headers = ['日期', '收入', '订单数']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=12, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = alignment
            cell.border = border
        
        # 写入每日数据
        daily_revenue = revenue_stats.get('daily_revenue', [])
        for row, data in enumerate(daily_revenue, 13):
            ws.cell(row=row, column=1, value=data['date'])
            ws.cell(row=row, column=2, value=f"¥{data['revenue']:,.2f}")
            ws.cell(row=row, column=3, value="-")  # 订单数列暂时用"-"填充
            
            # 设置边框
            for col in range(1, 4):
                ws.cell(row=row, column=col).border = border
        
        # 设置列宽
        column_widths = [15, 15, 12]
        for col, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(col)].width = width